package com.sk.ccflower.server.profile.xmlrpc;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Map;

import org.apache.log4j.Logger;

import com.sk.ccflower.lib.commonBeans.DefaultParamBean;
import com.sk.ccflower.lib.commonBeans.JsonResultBean;
import com.sk.ccflower.lib.commonBeans.XmlRpcResultBean;
import com.sk.ccflower.lib.commonUtils.ObjectUtil;
import com.sk.ccflower.lib.commonUtils.PhotoUtil;
import com.sk.ccflower.lib.define.BaseCfg;
import com.sk.ccflower.lib.define.Consts;
import com.sk.ccflower.lib.define.DBTable;
import com.sk.ccflower.lib.pfBeans.ProfileBean;
import com.sk.ccflower.lib.pfBeans.ProfileLoginBean;
import com.sk.ccflower.lib.pfBeans.ProfileModifyBean;
import com.sk.ccflower.lib.pfBeans.ProfileRegistBean;
import com.sk.ccflower.lib.util.JsonMaker;
import com.sk.ccflower.server.common.xmlRpcManager.BaseXmlRpcWorker;
import com.sk.ccflower.server.lib.dbBeans.CfProfileBean;
import com.sk.ccflower.server.lib.dbUtils.ConnectionPoolManager;
import com.sk.ccflower.server.lib.dbUtils.DBConnection;
import com.sk.ccflower.server.lib.dbUtils.qry.CfProfileQry;
import com.sk.ccflower.server.lib.dbUtils.qry.CfProfileShareQry;
import com.sk.ccflower.server.lib.dbUtils.qry.CfSpamListQry;
import com.sk.ccflower.server.lib.dbUtils.qry.MfCommentQry;
import com.sk.ccflower.server.lib.sysUtils.Environment;
import com.sk.ccflower.server.lib.sysUtils.MessageQueueUtil;

public class ProfileCenterXmlRpc extends BaseXmlRpcWorker {
	
	private Logger m_log;
	
	public ProfileCenterXmlRpc( ConnectionPoolManager dbcp ) {
		super( dbcp );
		
		this.m_log = Logger.getLogger( getClass() );
		
		this.m_log.debug( "ProfileCenterXmlRpc Constructor called! : "+super.mConnectionPool );
	}
	
	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		
		this.m_log.debug( "ProfileCenterXmlRpc finalize() called!" );
	}
	
	public JsonResultBean testJsonWork( String email, String passwd ) throws Exception {
		JsonResultBean retBean = new JsonResultBean();
		
		Map<String, String> result = new Hashtable<String, String>();
		result.put( "email", email );
		result.put( "passwd", passwd );
		
		try {
			this.m_log.debug("before json convert... : email("+email+") passwd("+passwd+")");
			JsonMaker jm = new JsonMaker();
			retBean.data = jm.toString( result );
			this.m_log.debug( "retBean.data : "+retBean.data );
		} catch ( Exception e ) {
			this.m_log.error( e );
			e.printStackTrace();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean testImageWork( String arg1 ) throws Exception {
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			String src = Environment.getCcFlowerHome()+"/data/profile/eunwoogi.jpg";
			File file = new File( src );
			FileInputStream fis = new FileInputStream( file );
			retBean.resultBytes = new byte[(int) file.length()];
			fis.read( retBean.resultBytes );
			fis.close();
			
			this.m_log.debug( "### ProfileCenterXmlRpc.testImageWork() called! : arg1("+arg1+") resultBytes's length("+retBean.resultBytes.length+")" );
			retBean.resultMsg = "SUCCESS";
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = "FAILUE";
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean registAvailableCheckWork() throws Exception {
		DBConnection dbconn = null;
		Statement stmt = null;
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			Connection con = dbconn.getConnection();
			stmt = con.createStatement(
				ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_READ_ONLY
			);
			
			String sql = "SELECT COUNT(email) FROM " + DBTable.CF_PROFILE;
			ResultSet rs = stmt.executeQuery( sql );
			int currMemberCount = 0;
			if ( rs.next() ) {
				currMemberCount = rs.getInt( 1 );
			}
			rs.close();
			
			m_log.debug( "AVAILABLE_MEMBER_COUNT : "+BaseCfg.AVAILABLE_MEMBER_COUNT );
			m_log.debug( "currMemberCount : "+currMemberCount );
			if ( currMemberCount >= BaseCfg.AVAILABLE_MEMBER_COUNT ) {
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_EXCEPTION_DENY;
			} else {
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_SUCCESS;
			}
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = e.getMessage();
		} finally {
			if ( null != stmt )		stmt.close();
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean registerWork(ProfileRegistBean bean) throws Exception {
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		String tableName = "ccFlower.cf_profile";
		String fieldName = "email, passwd, name, alias, sex, birth_year, birth_day, corp_cd, nation, language, phone";
		try {
			if(sqlInsert(tableName, fieldName, bean)==1){
				retBean.resultMsg = "REGIST SUCCESS";
				
				// 이성 매칭
				ProfileBean pBean = new ProfileBean();
				ObjectUtil.objectCopy( pBean, bean, false );
				MessageQueueUtil.sendMsg( BaseCfg.MESSAGE_QUEUE_SERVER_URL, Consts.MESSAGE_QUEUE_NAME.MATCHING, pBean );
			}else{
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
				retBean.resultMsg = "REGIST FAILED";
			}
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = "FAILUE";
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			
		}
		
		return retBean;
	}
	
	public int sqlInsert(String tableName, String fieldName, ProfileRegistBean bean) throws Exception {
		String SqlVal = "insert into "+tableName+"("+fieldName+") values('"+bean.email+"','"+bean.password+"','"+bean.name+"','"+bean.alias+"',"+bean.sex+",'"+bean.birthyear+"','"+bean.birthday+"','"+bean.addr+"','"+bean.nation+"','"+bean.language+"','"+bean.matching_area+"','"+bean.phone+"','"+bean.pic_name_e+"','"+bean.pic_name_n+"','"+bean.pic_name_m+"','"+bean.pic_name_f+"','"+bean.pic_name_add1+"','"+bean.pic_name_add2+"','"+bean.pic_name_add3+"')";
		m_log.debug( "query : "+SqlVal );
		int val=0; // 결과값:0=에러/1=성공
		Connection conn = dbConnect(); // db connection method
		
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
			val = stmt.executeUpdate(SqlVal);
		} catch(Exception ex) {
			this.m_log.error(ex);
			ex.printStackTrace();
			throw ex;
		}
		stmt.close();
		conn.close();
		return val;
	}
	
	public XmlRpcResultBean emailCheckWork( String email ) throws Exception {
		DBConnection dbconn = null;
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			CfProfileQry qry = new CfProfileQry( dbconn );
			qry.select( email );
			if ( qry.m_arr.size() == 0 ) {
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_SUCCESS;
			} else {
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_EXCEPTION_DUPL;
				retBean.resultMsg = "이미 가입된 이메일 주소입니다.";
			}
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = e.getMessage();
		} finally {
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean loginWork( ProfileLoginBean bean ) throws Exception {
		DBConnection dbconn = null;
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			
			CfProfileQry qry = new CfProfileQry( dbconn );
			qry.selectForLogin( bean.email, bean.password );
			if ( qry.m_arr.size() == 0 ) {
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
				retBean.resultMsg = "로그인 정보가 정확하지 않습니다.";
			} else {
				CfProfileBean dbBean = (CfProfileBean) qry.m_arr.get(0);
				ProfileBean pbean = new ProfileBean();
				ObjectUtil.objectCopy( pbean, dbBean, false );
				retBean.resultObj = pbean;
				
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_SUCCESS;
			}
		} catch ( Exception e ) {
			m_log.error( e.getMessage() );
			e.printStackTrace();
		} finally {
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean loginWork(String email, String password) throws Exception {
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		try {
			String tableName = "cc_flower.cf_profile";
			String fieldName = "email, passwd";
			if(sqlSelect(tableName, fieldName, email, password)==1){
				retBean.resultMsg = "LOGIN SUCCESS";
			}else{
				retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
				retBean.resultMsg = "LOGIN FAILED";
			}
			m_log.debug( "resultCode : "+retBean.resultCode );
			m_log.debug( "resultMsg : "+retBean.resultMsg );
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = "FAILUE";
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			
		}
		
		return retBean;
	}
	
	public int sqlSelect(String tableName, String fieldName, String _email, String _password) throws Exception {
		String resultRs="";
		String SqlVal = "select * from "+tableName+" where email='"+_email+"' and passwd='"+_password+"'";
		m_log.debug( "query : "+SqlVal );
		int val=0; // 결과값:0=에러/1=성공
		Connection conn = dbConnect(); // db connection method
		
		Statement stmt = null;
		ResultSet rs = null;
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(SqlVal);
	
			if (stmt.execute(SqlVal)) {
				rs = stmt.getResultSet();
			}
		}catch(Exception ex) {
			this.m_log.error(ex);
			ex.printStackTrace();
			throw ex;
		}

		try{
			while (rs.next()) {
				String email = rs.getString("email");
				String name = rs.getString("name");
				resultRs += email+"/"+name;
			} 
		}catch(Exception ex) {
		}
		
		if(resultRs != ""){
			val = 1;
		}
		m_log.debug( "### resultRs("+resultRs+") val("+val+")" );
		
		stmt.close();
		conn.close();
		return val;
	}
	
	public XmlRpcResultBean modifyWork( ProfileModifyBean bean ) throws Exception {
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		try {
			String tableName = "ccFlower.cf_profile";
			String fieldValue = "passwd='"+bean.password+"', alias='"+bean.alias+"', birth_year='"+bean.birthyear+"', birth_day='"+bean.birthday+"', nation='"+bean.nation+"', language='"+bean.language+"', matching_area='"+bean.matching_area+"', pic_name_add1='"+bean.pic_name_add1+"', pic_name_add2='"+bean.pic_name_add2+"', pic_name_add3='"+bean.pic_name_add3+"' where email='"+bean.email+"'";
			
			if(sqlUpdate(tableName, fieldValue)==1){
				retBean.resultMsg = "Modify SUCCESS";
			}else{
				retBean.resultMsg = "Modify FAILED";
			}
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = "FAILUE";
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			
		}
		
		return retBean;
	}
	
	public int sqlUpdate(String tableName, String fieldValue) throws Exception {
		String SqlVal = "update "+tableName+" set "+fieldValue;
		int val=0; // 결과값:0=에러/1=성공
		Connection conn = dbConnect(); // db connection method
		
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
			val = stmt.executeUpdate(SqlVal);
		} catch(Exception ex) {
			this.m_log.error(ex);
			ex.printStackTrace();
			throw ex;
		}
		stmt.close();
		conn.close();
		return val;
	}
	
	public XmlRpcResultBean initScoreWork(DefaultParamBean bean) throws Exception {
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		DBConnection dbconn = null;
		
		try{
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			MfCommentQry mfcQry = new MfCommentQry(dbconn);
			mfcQry.scoreInit( bean );
			retBean.resultMsg = "SUCCESS - "+bean.src_user;
		}catch(Exception ex){
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			retBean.resultMsg = "FAILUE";
			ex.printStackTrace();
			throw ex;
		} finally {
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean profileWork(String email) throws Exception {
		DBConnection dbconn = null;
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			CfProfileQry qry = new CfProfileQry(dbconn);
			qry.select( email );
			
			CfProfileBean pBean = (CfProfileBean) qry.m_arr.get(0);
			ProfileBean bean = new ProfileBean();
			ObjectUtil.objectCopy( bean, pBean, false );
			
			bean.img_profile = PhotoUtil.getDefaultPhoto( bean.email, Consts.PHOTO_TYPE.PROFILE );
			
			retBean.resultObj = bean;
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	public XmlRpcResultBean friendProfileWork( String src_user, String dest_user ) throws Exception {
		DBConnection dbconn = null;
		XmlRpcResultBean retBean = new XmlRpcResultBean();
		
		try {
			dbconn = new DBConnection( super.mConnectionPool.getConnection() );
			CfProfileQry qry = new CfProfileQry( dbconn );
			qry.select( dest_user );
			CfProfileShareQry sQry = new CfProfileShareQry( dbconn );
			sQry.select( dest_user, src_user );
			
			CfProfileBean pBean = (CfProfileBean) qry.m_arr.get(0);
			ProfileBean bean = new ProfileBean();
			ObjectUtil.objectCopy( bean, pBean, false );
			
			bean.img_profile = PhotoUtil.getDefaultPhoto( bean.email, Consts.PHOTO_TYPE.PROFILE );
			
			CfSpamListQry bQry = new CfSpamListQry( dbconn );
			bQry.selectBlockCheck( dest_user, src_user );
			if ( bQry.m_arr.size() > 0 ) {
				bean.block_user = true;
			}
			
			sQry.select( src_user, dest_user );
			if ( sQry.m_arr.size() > 0 ) {
				bean.share_info = true;
			}
			
			retBean.resultObj = bean;
		} catch ( Exception e ) {
			retBean.resultCode = XmlRpcResultBean.XML_RPC_RESULT_FAILURE;
			
			this.m_log.error( e );
			e.printStackTrace();
			throw e;
		} finally {
			if ( null != dbconn )	dbconn.close();
		}
		
		return retBean;
	}
	
	// 새로 작성한 디비 커넥션 부분	
	public Connection dbConnect() throws Exception, SQLException{
		//ConnectionPoolManager cpm = new ConnectionPoolManager(BaseCfg.JDBC_URL, BaseCfg.JDBC_USER, BaseCfg.JDBC_PWD);
		ConnectionPoolManager cpm = super.mConnectionPool;
		Connection conn = null; 
		try {
			conn = cpm.getConnection();
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return conn;
	}
}
